﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using IHISCETeim.BLL;

using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using IHISCETeim.Library;

namespace IHISCETeim.Web.Export.FTradeModel
{
    public partial class FTradeCatheringList : ExportPageBase
    {
        protected override string xlsTemplateFileName
        {
            get
            {
                return "应收帐款_财务部收款清单.xls";
            }
        }
        protected override string SheetName
        {
            get
            {
                return "Sheet1";
            }

        }

        #region 属性 (查询条件)


        protected string JingXiaoShang
        {
            get
            {
                return GetRequestValue<string>("JingXiaoShang", "");

            }
        }
        protected string KuanXiangLeiXing
        {
            get
            {
                return GetRequestValue<string>("KuanXiangLeiXing", "");

            }
        }

        protected string State
        {
            get
            {
                return GetRequestValue<string>("State", "");
            }
        }


        protected string ShoukuanRiQiFrom
        {
            get
            {
                return GetRequestValue<string>("ShoukuanRiQiFrom", "");

            }
        }
        protected string ShoukuanRiQiTo
        {
            get
            {
                if (string.IsNullOrEmpty(ShoukuanRiQiFrom))
                {
                    return "";
                }
                else
                {
                    string todate = GetRequestValue<string>("ShoukuanRiQiTo", "");
                    if (string.IsNullOrEmpty(todate))
                        return Convert.ToDateTime(ShoukuanRiQiFrom).AddDays(1).ToShortDateString();
                    else
                        return todate;
                }

            }
        }
        /// <summary>
        /// 记帐日期 表FTradeBalance
        /// </summary>
        protected string CreateDateFrom
        {
            get
            {
                return GetRequestValue<string>("CreateDateFrom", "");

            }
        }
        /// <summary>
        /// 记帐日期 表FTradeBalance
        /// </summary>
        protected string CreateDateTo
        {
            get
            {
                if (string.IsNullOrEmpty(CreateDateFrom))
                {
                    return "";
                }
                else
                {
                    string todate = GetRequestValue<string>("CreateDateTo", "");
                    if (string.IsNullOrEmpty(todate))
                        return Convert.ToDateTime(CreateDateFrom).AddDays(1).ToShortDateString();
                    else
                        return todate;
                }

            }
        }
        /// <summary>
        /// 原收款ID,如果大于0,则表示用于查看调帐关联记录
        /// </summary>
        protected int FCatheringId
        {
            get
            {
                return GetRequestValue<int>("FCatheringId", 0);
            }
        }

        #endregion

        protected string GetSQLWhere()
        {
            //营业经理权限 2014-02-21
            // string agentList = base.FTradeAgentPowerSqlWhere();
            string wheresql = "";// string.IsNullOrEmpty(agentList) ? " 1=1 " : string.Format(" JingXiaoShang in ({0})", agentList);

            string sqlcommonFiled = "";
            if (!string.IsNullOrEmpty(JingXiaoShang))
            {
                sqlcommonFiled = QueryOperatorsEnumHelper.ConvertCommonOperator("JingXiaoShang", QueryOperatorsEnum.等于, JingXiaoShang);
                if (!string.IsNullOrEmpty(wheresql))
                {
                    wheresql += " and " + sqlcommonFiled;
                }
                else
                {
                    wheresql = sqlcommonFiled;
                }
            }



            if (!string.IsNullOrEmpty(KuanXiangLeiXing))
            {
                string DOWNSQL = QueryOperatorsEnumHelper.ConvertCommonOperator(" KuanXiangLeiXing ", QueryOperatorsEnum.等于, KuanXiangLeiXing);
                if (KuanXiangLeiXing == "其他")
                {
                    DOWNSQL = " KuanXiangLeiXing not in('电汇','汇票','融资','调帐') ";
                }
                if (!string.IsNullOrEmpty(wheresql))
                {
                    wheresql += " and " + DOWNSQL;
                }
                else
                {
                    wheresql = DOWNSQL;
                }
            }


            if (!string.IsNullOrEmpty(ShoukuanRiQiFrom))
            {
                string DOWNSQL = QueryOperatorsEnumHelper.ConvertCommonOperator(" ShoukuanRiQi ", QueryOperatorsEnum.范围, ShoukuanRiQiFrom + "|" + ShoukuanRiQiTo);
                if (!string.IsNullOrEmpty(wheresql))
                {
                    wheresql += " and " + DOWNSQL;
                }
                else
                {
                    wheresql = DOWNSQL;
                }
            }

            if (!string.IsNullOrEmpty(State))
            {
                string DOWNSQL = QueryOperatorsEnumHelper.ConvertCommonOperator(" State ", QueryOperatorsEnum.等于, State);
                if (!string.IsNullOrEmpty(wheresql))
                {
                    wheresql += " and " + DOWNSQL;
                }
                else
                {
                    wheresql = DOWNSQL;
                }
            }

            return wheresql;
        }
        protected string GetSqlWhere2()
        {
            if (!string.IsNullOrEmpty(CreateDateFrom))
            {
                string DOWNSQL = QueryOperatorsEnumHelper.ConvertCommonOperator(" CreateDate ", QueryOperatorsEnum.范围, CreateDateFrom + "|" + CreateDateTo);

                return DOWNSQL;
            }
            return "";
        }
        protected void Page_Load(object sender, EventArgs e)
        {
            DataTableCollection dts = null;
            int TotalCount = 0;
            dts = FTradeCatheringController.GetPagination(GetSQLWhere(), GetSqlWhere2(), 1, 999999999, " FCatheringId desc ", ref TotalCount);

            InitializeWorkbook();
            ISheet sheet1 = hssfworkbook.GetSheet(SheetName);
            //
            DataTable dtList = dts[0];

            int rowindexBase = 1;
            int rowindex = 0;
            int rowReal = rowindex + rowindexBase;


            foreach (DataRow listDTDR in dtList.Rows)
            {
                int cellReal = 0;
                HSSFRow writerow = (HSSFRow)sheet1.CreateRow(rowReal);
                writerow.CreateCell(cellReal++).SetCellValue(listDTDR["SourceTag"].ToString());
                writerow.CreateCell(cellReal++).SetCellValue(listDTDR["JingXiaoShang"].ToString());
                writerow.CreateCell(cellReal++).SetCellValue(Convert.ToDateTime(listDTDR["ShoukuanRiQi"]));
                writerow.CreateCell(cellReal++).SetCellValue(GetDBDouble(listDTDR["JinE"]));
                writerow.CreateCell(cellReal++).SetCellValue(listDTDR["KuanXiangLeiXing"].ToString());

                writerow.CreateCell(cellReal++).SetCellValue(GetDBDouble(listDTDR["ShengYuJinE"]));
                writerow.CreateCell(cellReal++).SetCellValue(listDTDR["State"].ToString());
                writerow.CreateCell(cellReal++).SetCellValue(GetDBDouble(listDTDR["DongJieJinE"]));
                writerow.CreateCell(cellReal++).SetCellValue(listDTDR["IsAdjustable"].ToString());
                writerow.CreateCell(cellReal++).SetCellValue(listDTDR["RealName"].ToString());
                writerow.CreateCell(cellReal++).SetCellValue(listDTDR["FCTitle"].ToString());
                writerow.CreateCell(cellReal++).SetCellValue(listDTDR["Remark"].ToString());
                rowReal++;
            }

            //HSSFRow writerowSum = (HSSFRow)sheet1.GetRow(0);
            //// writerowSum.CreateCell(0).SetCellValue("总计");
            //writerowSum.CreateCell(9).SetCellFormula(string.Format("sum(J3:J{0})", rowReal));
            //writerowSum.CreateCell(10).SetCellFormula(string.Format("sum(K3:K{0})", rowReal));
            //writerowSum.CreateCell(11).SetCellFormula(string.Format("sum(L3:L{0})", rowReal));
            //writerowSum.CreateCell(13).SetCellFormula(string.Format("sum(N3:N{0})", rowReal));
            //writerowSum.CreateCell(14).SetCellFormula(string.Format("sum(O3:O{0})", rowReal));
            //writerowSum.CreateCell(15).SetCellFormula(string.Format("sum(P3:P{0})", rowReal));
            //writerowSum.CreateCell(2).SetCellFormula(string.Format("sum(C3:C{0})", rowReal));          

            //
            WriteToFile();
        }
        private Double GetDBDouble(object v)
        {
            if (v is DBNull)
                return 0;
            return Convert.ToDouble(v);
        }
        protected override string GetXlsFileName(string oldFileName)
        {
            //return base.GetXlsFileName(oldFileName);

            return string.Format("{0}{1}.xls", "应收帐款收款清单", DateTime.Now.ToString("yyyy-MM-dd")).Trim();
        }
    }
}